package com.javabloger.orm;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.javabloger.orm.common.ORMappingTools;
import com.javabloger.orm.common.TableMappingTools;
import com.javabloger.orm.exception.ObjectSQLException;

public class PersistenceManager  implements IObjectDaos {
	
		private DataSource dataSource;
		private ResultSet rs=null;
		private PreparedStatement pstmt=null;
		private  Connection connection;
		
		private static Log logger = LogFactory.getLog(PersistenceManager.class);
		private static ORMappingTools   MappingTools= new ORMappingTools();
		private static TableMappingTools   tableMappingTools= new TableMappingTools();
		
		
		public PersistenceManager(DataSource dataSource ){
			 super();
			 this.dataSource=dataSource;
		}
		
		public PersistenceManager(){  
			super();
			dataSource = null;
		}
		
		
//		public Connection getConnection() {
//				try{
//					connection =this.dataSource.getConnection();
//				}
//				catch (Exception e){
//						e.printStackTrace();
//					}
//				return connection;
//		}

		
		public DataSource getDataSource() {
			return this.dataSource;
		}
		
		
		/**
		  * @param conn  设置数据库连接池属性  
		  * <p>
		  */
		public synchronized void setDataSource(DataSource dataSource){
			if (logger.isDebugEnabled() ){logger.debug("setDataSource .................");	}
			try {
//				dataSource.getConnection().setAutoCommit(false);
			} catch ( Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			this.dataSource=dataSource;
		}
		 
		 /**
		  * @param conn 设置数据库连接属性  
		  * <p>
		  */
		public synchronized void setConnection(Connection connection){
			if (logger.isDebugEnabled() ){
				logger.debug("setConnection .................");
			} 
			try {
//				connection.setAutoCommit(false);
			} catch ( Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			this.connection=connection;
		}
		
		
		/**
		 * @param Object []    批量插入的对象数组
		 *  <p>
		 */
		public synchronized void  addBatchObjects (   Object []  objTables ) throws  Exception {
			if (logger.isDebugEnabled() ){logger.debug("addBatchObjects ................."); } 
			PreparedStatement pstmt=null;
			try{
				for (int i=0;i<objTables.length ;i++){

					String table=MappingTools.getTableNameForObject(objTables[i]  );
					
					List<String> column=MappingTools.getObjectColumnName(objTables[i]);
				 
					Map<?, ?> map =  MappingTools.copyABeanToMap(objTables[i] ) ;	 
					
					StringBuffer sBuffer = new StringBuffer();
					sBuffer.append( "insert into ").append(table).append(" (");
					for (int k=0;k<column.size();k++){
						sBuffer.append(  column.get(k)   ).append(",");
					}
					sBuffer.append(") values ("  );
					
					for (int h=0;h<column.size();h++){
						sBuffer.append("?,");
					}
					sBuffer.append(")");
					
	 				String SQL= sBuffer.toString().replaceAll("\\,\\)", " )")  ;
					logger.info( "SQL  ["+SQL+"]" );
					if (connection==null){
							connection = this.prepareConnection();
					}
	 	  			pstmt=connection.prepareStatement(SQL);
	 	  			
	 	  			for (int j=0;j<column.size();j++){
	 	  				System.out.println (j+1+"," +map.get( column.get(j) ) );
	 	  				pstmt.setObject(j+1, map.get( column.get(j) ));
	 	  			}
	 	  				pstmt.addBatch();
	 	  				pstmt.executeBatch() ;
				}
			}
				catch (Exception e){
					logger.error(e);
					connection.rollback();
					throw new ObjectSQLException(e);
				}
				
				finally {
					if (connection.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						connection.close();
					}
					}
				 
			}
					
		
		protected Connection prepareConnection() throws SQLException {
		        if(this.getDataSource() == null) {
		            throw new SQLException("DataSource Connection  is null  .... ");
		        }
		        return this.getDataSource().getConnection();
		  }
		
		
		
		/**
		 * <p>
		 * @param String  执行的SQL语句
		 *  <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 */
		public synchronized Map<?, ?>  queryMap (  String SQL ) throws  Exception {
			if (logger.isDebugEnabled() ){
				logger.debug("queryMap .................");
				logger.debug("[ SQL "+SQL+" ]"+" .................");
			} 
			Map<?, ?>  result =null;
			Connection conn = this.prepareConnection();
			try{
				logger.info(conn);
				pstmt=conn.prepareStatement(SQL);
				rs=pstmt.executeQuery();
				result =MappingTools.resultToMap(rs)  ;
				}
				catch (Exception e){
					logger.error(e);
					throw new ObjectSQLException(e);
				}
				
				finally {
					if (conn.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						rs.close();
						pstmt.close();
						conn.close();
					}
					}
					return result ;
			}
		
		
		/**
		 * <p>
		 * @param String  执行的SQL语句
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 * <p>
		 * @param param  查询条件
		 */
		public Map<?, ?>  queryMapByCondition (   String SQL,  Object [] param ) throws  Exception {
			if (logger.isDebugEnabled() ){
				logger.debug("queryMapByBindCondition ................." + SQL);
			} 
			
			Connection conn =this.prepareConnection();
			
			Map<?, ?>  result =null;
			try{
				PreparedStatement pstmt=conn.prepareStatement(SQL);
				if (param!=null){
					for (int i=0;i<param.length;i++){
						pstmt.setObject(i+1,param[i]  );
					}
				}  // if 
				
				ResultSet rs=pstmt.executeQuery();
				 result =MappingTools.resultToMap(rs)  ;
			}
			catch (Exception e){
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			
			finally {
				if (conn.getAutoCommit()==true){
					if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
					conn.close();
				}
				}
				return result ;
		}
		
		
		/**
		 * <p>
		 * @param String  执行的SQL语句
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 * <p>
		 * @param param  查询条件
		 */
		public List<?> queryListByCondition (  String SQL, Class<?> objTable,Object [] param ) throws  Exception{
			if (logger.isDebugEnabled() ){
				logger.debug("queryListBySQLCondition .................["+SQL+"]");
			} 
			List<?> obj =null;
			if (connection==null){
				connection=this.prepareConnection();
			}
			try{
			PreparedStatement pstmt=connection.prepareStatement(SQL);
			if (param!=null){
				for (int i=0;i<param.length;i++){
					pstmt.setObject(i+1,param[i]  );
				}
			}
		
			ResultSet rs=pstmt.executeQuery();
				obj =MappingTools.toBeanList(rs, objTable );
			}
			catch (Exception e){
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			
			finally {
				if (connection.getAutoCommit()==true){
					if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
					connection.close();
				}
				}
				return obj ;
		}
		
		
		/**
		 * <p>
		 * @param String  执行的SQL语句
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 */
		public  List  queryList (  String SQL, Class<?>  objTable ) throws  Exception {
			
			Connection conn=this.prepareConnection();
			
			List  list =null;
			try{
				long startRunSQL=System.currentTimeMillis();
					PreparedStatement pstmt=conn.prepareStatement(SQL);
					ResultSet rs=pstmt.executeQuery();
					 
					
				long endRunSQL =System.currentTimeMillis()-startRunSQL;
				list =  tableMappingTools .toBeanList(rs, objTable);
				System.out.println ( list );
 
				if (logger.isDebugEnabled() ){
						if(endRunSQL>500) {logger.warn("Warnning , Check This SQL : [ "+SQL+"] ,Expend "+endRunSQL +" ms " );}
						else{
							logger.debug("queryList  [ SQL:  "+SQL+" ]"+" ................."+endRunSQL  +" ms ");
						}
				} 
			
			}
			catch (Exception e){
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			finally {
				if (conn.getAutoCommit()==true){
					if (logger.isDebugEnabled() ){  logger.debug("conn  is closed !");	} 
					conn.close();
				}
			}
			return list ;
		}
				
		
		/**
		 * <p>
		 * @param String  执行的SQL语句
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 */
		public Object  queryOneObject (   String SQL, Class<?> objTable ) throws  Exception{
		
			Object obj=null;
			Connection	conn=this.prepareConnection();
			
			try{
			long startRunSQL=System.currentTimeMillis();
				PreparedStatement pstmt=conn.prepareStatement(SQL);
				ResultSet rs=pstmt.executeQuery();
			long endRunSQL =System.currentTimeMillis()-startRunSQL;
			obj =MappingTools.toBean (rs, objTable );
			
			if (logger.isDebugEnabled() ){
				if(endRunSQL>500) {logger.warn("Warnning , Check This SQL : [ "+SQL+"] ,Expend "+endRunSQL +" ms " );}
				else{logger.debug("queryOneObject  [ SQL:  "+SQL+" ]"+" ................."+endRunSQL  +" ms ");	}
			} 
			
			}
			catch (Exception e){
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			finally {
				if (conn.getAutoCommit()==true){
					if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
					conn.close();
				}
			}
			return obj ;
		}
		
		
		/**
		 * @param String  执行的SQL语句
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 * <p>
		 * @param param  查询参数
		 */
		public Object  queryOneObjectByCondition (   String SQL, Class<?> objTable, Object [] param ) 	throws  Exception{
		 
			Connection conn=this.prepareConnection();
		 
			Object obj=null;
			try{
			long startRunSQL=System.currentTimeMillis();
				PreparedStatement pstmt=conn.prepareStatement(SQL);
			long endRunSQL =System.currentTimeMillis()-startRunSQL;
			if (param!=null){
				for (int i=0;i<param.length;i++){
					pstmt.setObject(i+1,param[i]  );
				}
			}
			ResultSet rs=pstmt.executeQuery();
			obj =MappingTools.toBean (rs, objTable );
			
			if (logger.isDebugEnabled() ){
				if(endRunSQL>500) {logger.warn("Warnning , Check This SQL : [ "+SQL+"] ,Expend "+endRunSQL +" ms " );}
				else{logger.debug("queryOneObject  [ SQL:  "+SQL+" ]"+" ................."+endRunSQL  +" ms ");	}
			} 
			
			}
			catch (Exception e){
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			finally {
				if (conn.getAutoCommit()==true){
					if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
					conn.close();
				}
			}
			return obj ;
		}
		
		
		/**
		 * @param String  执行的SQL语句
		 */
		public void updateObjectBySQL( String SQL  ) throws SQLException {
			if (logger.isDebugEnabled() ){
				logger.debug("updateObjectBySQL .................");
				logger.debug("[ SQL "+SQL+" ]"+" .................");
			} 
			
			if (connection==null) {	
				connection=this.prepareConnection();
			}
			try{
 	 	  			PreparedStatement pstmt=connection.prepareStatement(SQL);
  	 	  			pstmt.executeUpdate();
				}
				catch (Exception e){
					logger.error(e);
					connection.rollback();
					throw new ObjectSQLException(e);
				}
				finally {
					if (connection.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						connection.close();
					}
				}
			}
		
		
		/**
		 * @param objTable  实例化 (new)  pojo 对象
		 * <p>
		 * @param KeyColumn  更新条件字段名称
		 * <p>
		 * @param KeyValue	  更新条件字段值
		 */
		public void updateObjectByPrimaryKey( Object  objTable,String KeyColumn,Object KeyValue  )  throws  Exception {
					if (logger.isDebugEnabled() ){	logger.debug("updateObjectByPrimaryKey .................");	} 
					
					if (connection==null) {  
						connection=this.prepareConnection();
					}
			try{
					String table=MappingTools.getTableNameForObject(objTable);
					
					List<String> column=MappingTools.getObjectColumnName(objTable);
				 
 					Map<?, ?> map =  MappingTools.copyABeanToMap(objTable ) ;	
 					map.remove("class");
					
					StringBuffer sBuffer = new StringBuffer();
					sBuffer.append( "update ").append(table).append(" SET ");
					for (int i=0;i<column.size();i++){
						sBuffer.append(  column.get(i)   ).append("=?,");
					}
					String SQL1= sBuffer.substring(0,  sBuffer.lastIndexOf(",")    ) ;
					
					StringBuffer sBuffer1 = new StringBuffer();
					sBuffer1.append( SQL1 ).append(" where  ").append(KeyColumn).append("=?");
					
					if (logger.isDebugEnabled() ){
						logger.info( "SQL  ["+sBuffer1+"]" );
					} 
					
 	 	  			PreparedStatement pstmt=connection.prepareStatement(sBuffer1.toString());
 	 	  			for (int i=0;i<column.size();i++){
 	 	  				pstmt.setObject(i+1, map.get( column.get(i)  ) );
 	 	  			}
 	 	  			pstmt.setObject(column.size()+1, KeyValue );
 	 	  			
  	 	  			pstmt.executeUpdate();
				}
				catch (Exception e){
					logger.error(e);
					connection.rollback();
					throw new ObjectSQLException(e);
				}
				finally {
					if (connection.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						connection.close();
					}
				}
			}
		
		/**
		 *  插入记录方法
		 * <p>
		 * @param objTable  实例化 (new)  pojo 对象
		 */
		public synchronized  void addObject( Object objTable  ) throws  Exception {
			if (logger.isDebugEnabled() ){	logger.debug("addObject .................");		} 
			
			if (connection==null||connection.isClosed()) {	
				connection	 =this.prepareConnection();
			}
			try{
					String table=MappingTools.getTableNameForObject(objTable);
					
					List<String> column=MappingTools.getObjectColumnName(objTable);
				 
					Map<?, ?> map =  MappingTools.copyABeanToMap(objTable ) ;	 
					
					StringBuffer sBuffer = new StringBuffer();
					sBuffer.append( "insert into ").append(table).append(" (");
					for (int i=0;i<column.size();i++){
						sBuffer.append(  column.get(i)   ).append(",");
					}
					sBuffer.append(") values ("  );
					
					for (int i=0;i<column.size();i++){
						sBuffer.append("?,");
					}
					sBuffer.append(")");
					
	 				String SQL= sBuffer.toString().replaceAll("\\,\\)", " )")  ;
					
					if (logger.isDebugEnabled() ){	logger.info( "SQL  ["+SQL+"]" );	} 
					
	 	  			PreparedStatement pstmt=connection.prepareStatement(SQL);
	 	  			for (int i=0;i<column.size();i++){
	 	  				pstmt.setObject(i+1, map.get( column.get(i)   ));
	 	  			}
	 	  			pstmt.executeUpdate();
				}
				catch (Exception e){
					logger.error(e);
//					connection.rollback();
					throw new ObjectSQLException(e);
				}
				finally {
					if (connection.getAutoCommit()==true){
						 if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						 	connection.close();
					}
				}
			}
		

		 /**
		  * @param objTable 对象表名称
		  * <p>
		  * @param PrimaryKey 字段名称(主键)
		  * <p>
		  *  @param PrimaryKey 字段值(主键)
		  * <p>
		  */
		public void deleteByPrimaryKey( Class<?> objTable, Object PrimaryKey ,Object PrimaryValue) throws Exception {
			if (connection==null) {
				connection=this.prepareConnection();
			}
			try{
					String table=MappingTools.getTableNameForClass(objTable);
					StringBuffer sBuffer=new StringBuffer();
					sBuffer.append(" delete from ").append(table).append(" where ").append(PrimaryKey).append("=?");
					
					if (logger.isDebugEnabled() ){
						logger.debug("deleteByPrimaryKey .................");			
						logger.debug( "SQL  [ "+sBuffer+" ] " );					
					} 
					
					PreparedStatement pstmt=connection.prepareStatement(sBuffer.toString() );
 							pstmt.setObject( 1, PrimaryValue);
 							pstmt.executeUpdate();
			}
				catch (Exception e){
					logger.error(e);
					connection.rollback();
					throw new ObjectSQLException(e);
				}
				finally {
					if (connection.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						connection.close();
					}
				}
			}


		 /**
		  * @param objTable 对象表名称
		  * <p>
		  * @param PrimaryKey 字段名称(主键)
		  * <p>
		  *  @param PrimaryKey 多个字段值(主键)
		  * <p>
		  */
		public void deleteObjectsByPrimaryKeys( Class<?>  objTable,Object PrimaryKey, Object [] PrimaryValue ) throws Exception {
			if (logger.isDebugEnabled() ){
				logger.debug("deleteObjectsByPrimaryKeys .................");
			} 
			try{
				if (connection==null) {
					connection=this.prepareConnection();
				}
			String table=MappingTools.getTableNameForClass(objTable);
			
			StringBuffer sBuffer=new StringBuffer();
			sBuffer.append(" delete from ").append(table).append(" where ");
			
			if (logger.isDebugEnabled() ){			logger.info( "SQL  ["+sBuffer+"]" );					} 
			
			PreparedStatement pstmt=null;
			 
			Object[] obj1=(Object[]) PrimaryValue;
			for ( int i=0;i<obj1.length;i++){
				if (i==0){
					sBuffer.append(PrimaryKey ).append("=? ");
				}
				else{
					sBuffer.append("or ").append(PrimaryKey).append("=? ");
				}
			}
				pstmt=connection.prepareStatement(sBuffer.toString());
				for ( int i=0;i< obj1.length;i++){
					pstmt.setObject(i+1, obj1[i]);
				}
				
				pstmt.executeUpdate();
				}
				catch (Exception e){
					logger.error(e);
					connection.rollback();
					throw new ObjectSQLException(e);
				}
				finally {
					if (connection.getAutoCommit()==true){
						if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						connection.close();
					}
				}
			}

	 
		/**
		 * 结束事务
		 */
		public synchronized void endTransaction(  ) throws Exception {
			if (logger.isDebugEnabled() ){	logger.debug("endTransaction .................");	} 
			if (connection==null||connection.isClosed()) {	
				logger.debug("reconn .................");	
				connection=this.prepareConnection();	
				}
			try {
				connection.commit();
				if (logger.isDebugEnabled() ){	logger.debug("conn closed .................");	} 
				}
			catch ( Exception e) {
				logger.error(e);
				throw new ObjectSQLException(e);
			}
			finally {
				if (connection.isClosed()==false){
					if (connection.getAutoCommit()==true){
						 if (logger.isDebugEnabled() ){  logger.debug("conn is closed !");	} 
						 	connection.close();
					}
				}
				}
		}
		
		/**
		 * 开启事务
		 */
		public synchronized void startTransaction( ) throws SQLException {
				if (logger.isDebugEnabled() ){logger.debug("startTransaction .................");	} 
				if (connection==null) {	logger.debug(" new conn .................");	
					connection=this.prepareConnection() ; }
				try {
					connection.setAutoCommit(false);
					}
				catch (SQLException e) {
					throw new ObjectSQLException(e);
				}
			}

		
		
}
